﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Excel = Microsoft.Office.Interop.Excel;
using System.Windows.Forms;

namespace sams_scope
{
    class xlopen
    {
        Excel.Application xl;
        Excel.Workbook wb;
        Excel.Worksheet scope;
        Excel.Worksheet ws;
        Excel.Worksheet ews;
        Excel.Range wsa;
        Excel.Range wsb;
        Excel.Range wsc;
        Excel.Range wsd;
        Excel.Range sca;
        Excel.Range scb;
        Excel.Range scc;
        Excel.Range scd;
        Excel.Range rcr;
        Excel.Range scrcr;
        Excel.Range orng;
        OpenFileDialog diag;
        private object lws;
        private int count;
        private int scrc;
        private int rc;
        private object wsbdv;
        private string wsav;
        private string wsbv;
        private string wscv;
        private string wsdv;
        private string ewsn;
        private bool scopeexist;  
        
        internal void xlopenfile()
        {
            xl = new Excel.Application();           
            xl.Visible = true;            
            MessageBox.Show("Please select file when 'Open' dialog box pops up.", "File Select", MessageBoxButtons.OK, MessageBoxIcon.Information);
            diag = new OpenFileDialog();
            diag.Multiselect = false;
            if (diag.ShowDialog() == DialogResult.OK)
            { }
            Console.SetWindowSize(25, 15);
            Console.Write("Processing......");    
            wb = xl.Workbooks.Open(Filename:diag.FileName);
            wb.Application.Calculation = Excel.XlCalculation.xlCalculationManual;
            count = wb.Worksheets.Count;
            lws = wb.Worksheets["Own Prov Equip"];

            for (int e = 1; e < count +1; e++)
            {
                ews = wb.Worksheets[e];
                ewsn = ews.Name;
                scopeexist = (ewsn == "Scope");
                if (scopeexist == true)
                {
                    break;
                }
            }
           
            if (scopeexist == true)
            {
                var msg = MessageBox.Show("A Scope worksheet already exists! " +
                    "Do you wish for it to be deleted and re-created?", "Worksheet Exist", MessageBoxButtons.YesNo, MessageBoxIcon.Question);

                switch (msg)
                {
                    case DialogResult.Yes:
                        {
                            MessageBox.Show("The Scope worksheet will be deleted and re-built!", "Worksheet Delete", MessageBoxButtons.OK);
                            xl.DisplayAlerts = false;
                            ews.Delete();
                            xl.DisplayAlerts = true;
                        }
                        break;
                    case DialogResult.No:
                        {
                            var wbc = MessageBox.Show("There is no work to be done by this macro and will end.", "Macro Closing", MessageBoxButtons.OK, MessageBoxIcon.Warning);
                            if (wbc == DialogResult.OK)
                            {                                
                                diag.Dispose();
                                ews = null;
                                wb.Close(SaveChanges: false);
                                xl.Quit();                               
                                return;                                
                            }
                        }
                        break;
                }
            }

            else
            {
                MessageBox.Show("A worksheet named 'Scope' will be created showing only checked scope items",
                    "Worksheet Created", MessageBoxButtons.OK, MessageBoxIcon.Information);

            }

            scope = (Excel.Worksheet)wb.Worksheets.Add(After:lws);
            scope.Name = "Scope";
            scope.Range["A1"].Value = "Category";
            scope.Range["B1"].Value = "Sub-Category";
            scope.Range["C1"].Value = "Checked";
            scope.Range["D1"].Value = "Description";
                        
            for (int i = 3; i < count -1; i++)
            {
                ws = wb.Worksheets[i];
                rcr = ws.Cells[ws.Rows.Count, "A"];
                rc = rcr.End[Excel.XlDirection.xlUp].Row;
                for (int j = 1; j < rc; j++)
                {
                    wsa = ws.Cells[j, "A"];
                    wsb = ws.Cells[j, "B"];
                    wsc = ws.Cells[j, "C"];
                    wsd = ws.Cells[j, "D"];
                    wsav = wsa.Value;
                    wsbdv = wsb.Value;
                    wsbv = Convert.ToString(wsbdv);
                    wscv = wsc.Value;
                    wsdv = wsd.Value;

                    if ((wscv == "x" || wscv == "X") && !String.IsNullOrEmpty(wsav) && wsbv != null)
                    {
                        scrcr = scope.Cells[scope.Rows.Count, "A"];
                        scrc = scrcr.End[Excel.XlDirection.xlUp].Row;
                        sca = scope.Cells[scrc + 1, "A"];
                        scb = scope.Cells[scrc + 1, "B"];
                        scc = scope.Cells[scrc + 1, "C"];
                        scd = scope.Cells[scrc + 1, "D"];
                        sca.Value = wsav;
                        scb.Value = wsbv;
                        scc.Value = wscv;
                        scd.Value = wsdv;
                    }                   
                }
            }
            
            orng = scope.get_Range("A1", "D1");
            orng.EntireColumn.AutoFit();
            orng = scope.get_Range("A1", "C1");
            orng.EntireColumn.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
            orng = scope.get_Range("D1");
            orng.EntireColumn.HorizontalAlignment = Excel.XlHAlign.xlHAlignLeft;            
            orng = scope.get_Range("A2", "D" + (scrc + 1) + "");
            orng.Font.Name = "Tahoma";
            orng.Font.Size = "8";
            orng.Cells.Borders[Excel.XlBordersIndex.xlInsideHorizontal].Weight = Excel.XlBorderWeight.xlThin;
            orng.Cells.Borders[Excel.XlBordersIndex.xlInsideVertical].Weight = Excel.XlBorderWeight.xlThin;
            orng.BorderAround(Type.Missing, Excel.XlBorderWeight.xlMedium, Excel.XlColorIndex.xlColorIndexAutomatic, Type.Missing);
            orng = scope.get_Range("A1:D1");
            orng.Font.Name = "Tahoma";
            orng.Font.Size = "8";
            orng.Cells.Borders[Excel.XlBordersIndex.xlInsideHorizontal].Weight = Excel.XlBorderWeight.xlThin;
            orng.Cells.Borders[Excel.XlBordersIndex.xlInsideVertical].Weight = Excel.XlBorderWeight.xlThin;
            orng.Cells.Borders[Excel.XlBordersIndex.xlEdgeBottom].Weight = Excel.XlBorderWeight.xlMedium;
            orng.BorderAround(Type.Missing, Excel.XlBorderWeight.xlMedium, Excel.XlColorIndex.xlColorIndexAutomatic, Type.Missing);
            orng.Font.Bold = true;

            sca = null;
            scb = null;
            scc = null;
            scd = null;
            wsa = null;
            wsb = null;
            wsc = null;
            wsd = null;
            ews = null;
            orng = null;
            scope = null;
            xl.DisplayAlerts = false;
            wb.Close(SaveChanges: true, Filename: diag.FileName);
            xl.DisplayAlerts = true;
            diag.Dispose();
            xl.Quit();            
        }        
    }
}
